package dao;

import util.DatabaseConnection;

import java.sql.*;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.Map;

/**
 * 统计数据访问对象
 */
public class StatisticsDAO {

    /**
     * 获取用户统计数据
     */
    public Map<String, Object> getUserStatistics(int userId) throws SQLException {
        Map<String, Object> stats = new HashMap<>();

        try (Connection conn = DatabaseConnection.getConnection()) {
            // 用户总预约次数
            String sql1 = "SELECT COUNT(*) as count FROM reservations WHERE user_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql1)) {
                stmt.setInt(1, userId);
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    stats.put("myReservations", rs.getInt("count"));
                }
            }

            // 用户今日预约
            String sql2 = "SELECT COUNT(*) as count FROM reservations WHERE user_id = ? AND reservation_date = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql2)) {
                stmt.setInt(1, userId);
                stmt.setDate(2, Date.valueOf(LocalDate.now()));
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    stats.put("myTodayReservations", rs.getInt("count"));
                }
            }

            // 用户总学习时长（小时）
            String sql3 = "SELECT SUM(TIMESTAMPDIFF(HOUR, start_time, end_time)) as total_hours " +
                    "FROM reservations WHERE user_id = ? AND status = 2";
            try (PreparedStatement stmt = conn.prepareStatement(sql3)) {
                stmt.setInt(1, userId);
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    stats.put("totalHours", rs.getInt("total_hours"));
                }
            }

            // 用户偏好时段
            String sql4 = "SELECT " +
                    "CASE " +
                    "WHEN start_time < '12:00:00' THEN '上午' " +
                    "WHEN start_time < '18:00:00' THEN '下午' " +
                    "ELSE '晚上' END as time_period, " +
                    "COUNT(*) as count " +
                    "FROM reservations WHERE user_id = ? " +
                    "GROUP BY time_period ORDER BY count DESC LIMIT 1";
            try (PreparedStatement stmt = conn.prepareStatement(sql4)) {
                stmt.setInt(1, userId);
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    stats.put("preferredTime", rs.getString("time_period"));
                } else {
                    stats.put("preferredTime", "暂无数据");
                }
            }
        }

        return stats;
    }

    /**
     * 获取系统统计数据
     */
    public Map<String, Object> getSystemStatistics() throws SQLException {
        Map<String, Object> stats = new HashMap<>();

        try (Connection conn = DatabaseConnection.getConnection()) {
            // 系统总预约数
            String sql1 = "SELECT COUNT(*) as count FROM reservations";
            try (PreparedStatement stmt = conn.prepareStatement(sql1);
                    ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    stats.put("totalReservations", rs.getInt("count"));
                }
            }

            // 今日预约数
            String sql2 = "SELECT COUNT(*) as count FROM reservations WHERE reservation_date = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql2)) {
                stmt.setDate(1, Date.valueOf(LocalDate.now()));
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    stats.put("todayReservations", rs.getInt("count"));
                }
            }

            // 座位利用率
            String sql3 = "SELECT " +
                    "(SELECT COUNT(*) FROM reservations WHERE status = 0 AND reservation_date = ?) * 100.0 / " +
                    "(SELECT COUNT(*) FROM seats WHERE base_status = 0) as utilization";
            try (PreparedStatement stmt = conn.prepareStatement(sql3)) {
                stmt.setDate(1, Date.valueOf(LocalDate.now()));
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    stats.put("utilization", Math.round(rs.getDouble("utilization")));
                }
            }

            // 热门时段
            String sql4 = "SELECT " +
                    "CASE " +
                    "WHEN start_time < '12:00:00' THEN '上午' " +
                    "WHEN start_time < '18:00:00' THEN '下午' " +
                    "ELSE '晚上' END as time_period, " +
                    "COUNT(*) as count " +
                    "FROM reservations WHERE reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) " +
                    "GROUP BY time_period ORDER BY count DESC LIMIT 1";
            try (PreparedStatement stmt = conn.prepareStatement(sql4);
                    ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    stats.put("popularTime", rs.getString("time_period"));
                } else {
                    stats.put("popularTime", "暂无数据");
                }
            }
        }

        return stats;
    }

    /**
     * 获取座位使用率统计
     */
    public Map<String, Object> getSeatUtilizationStats() throws SQLException {
        Map<String, Object> stats = new HashMap<>();

        String sql = "SELECT s.floor, COUNT(r.reservation_id) as reservation_count " +
                "FROM seats s LEFT JOIN reservations r ON s.seat_id = r.seat_id " +
                "AND r.reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) " +
                "GROUP BY s.floor ORDER BY s.floor";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                int floor = rs.getInt("floor");
                int count = rs.getInt("reservation_count");
                stats.put("floor_" + floor, count);
            }
        }

        return stats;
    }

    /**
     * 获取时间段统计
     */
    public Map<String, Object> getTimeSlotStats() throws SQLException {
        Map<String, Object> stats = new HashMap<>();

        String sql = "SELECT " +
                "CASE " +
                "WHEN start_time < '12:00:00' THEN 'morning' " +
                "WHEN start_time < '18:00:00' THEN 'afternoon' " +
                "ELSE 'evening' END as time_slot, " +
                "COUNT(*) as count " +
                "FROM reservations WHERE reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) " +
                "GROUP BY time_slot";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                String timeSlot = rs.getString("time_slot");
                int count = rs.getInt("count");
                stats.put(timeSlot, count);
            }
        }

        return stats;
    }

    /**
     * 获取楼层统计
     */
    public Map<String, Object> getFloorStats() throws SQLException {
        Map<String, Object> stats = new HashMap<>();

        String sql = "SELECT s.floor, COUNT(r.reservation_id) as reservation_count " +
                "FROM seats s LEFT JOIN reservations r ON s.seat_id = r.seat_id " +
                "AND r.reservation_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
                "GROUP BY s.floor ORDER BY s.floor";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                int floor = rs.getInt("floor");
                int count = rs.getInt("reservation_count");
                stats.put("floor_" + floor, count);
            }
        }

        return stats;
    }

    /**
     * 将ResultSet映射为Reservation对象（简化版）
     */
    @SuppressWarnings("unused")
    private model.Reservation mapResultSetToReservation(ResultSet rs) throws SQLException {
        model.Reservation reservation = new model.Reservation();
        reservation.setReservationId(rs.getInt("reservation_id"));
        reservation.setUserId(rs.getInt("user_id"));
        reservation.setSeatId(rs.getInt("seat_id"));
        reservation.setReservationDate(rs.getDate("reservation_date").toLocalDate());
        reservation.setStartTime(rs.getTime("start_time").toLocalTime());
        reservation.setEndTime(rs.getTime("end_time").toLocalTime());
        reservation.setStatus(rs.getInt("status"));

        Timestamp createTime = rs.getTimestamp("create_time");
        if (createTime != null) {
            reservation.setCreateTime(createTime.toLocalDateTime());
        }

        // 设置关联对象
        if (rs.getString("student_id") != null) {
            model.User user = new model.User();
            user.setUserId(rs.getInt("user_id"));
            user.setStudentId(rs.getString("student_id"));
            user.setName(rs.getString("user_name"));
            reservation.setUser(user);
        }

        if (rs.getString("seat_number") != null) {
            model.Seat seat = new model.Seat();
            seat.setSeatId(rs.getInt("seat_id"));
            seat.setSeatNumber(rs.getString("seat_number"));
            seat.setFloor(rs.getInt("floor"));
            reservation.setSeat(seat);
        }

        return reservation;
    }

    /**
     * 获取用户预约趋势数据
     */
    public Map<String, Object> getUserTrendData(int userId, String period) throws SQLException {
        Map<String, Object> trendData = new HashMap<>();

        String sql;
        String intervalCondition;
        String dateFormat;
        String[] labels;

        switch (period) {
            case "近一周":
                sql = "SELECT DAYOFWEEK(reservation_date) as day_of_week, " +
                        "SUM(TIMESTAMPDIFF(HOUR, start_time, end_time)) as hours " +
                        "FROM reservations WHERE user_id = ? " +
                        "AND reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) " +
                        "GROUP BY DAYOFWEEK(reservation_date) " +
                        "ORDER BY DAYOFWEEK(reservation_date)";
                labels = new String[] { "周日", "周一", "周二", "周三", "周四", "周五", "周六" }; // MySQL DAYOFWEEK: 1=Sunday
                break;
            case "近一月":
                sql = "SELECT WEEK(reservation_date, 1) - WEEK(DATE_SUB(reservation_date, INTERVAL DAYOFMONTH(reservation_date)-1 DAY), 1) + 1 as week_num, "
                        +
                        "SUM(TIMESTAMPDIFF(HOUR, start_time, end_time)) as hours " +
                        "FROM reservations WHERE user_id = ? " +
                        "AND reservation_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
                        "GROUP BY week_num " +
                        "ORDER BY week_num";
                labels = new String[] { "第1周", "第2周", "第3周", "第4周", "第5周" };
                break;
            case "近一年":
                sql = "SELECT MONTH(reservation_date) as month_num, " +
                        "SUM(TIMESTAMPDIFF(HOUR, start_time, end_time)) as hours " +
                        "FROM reservations WHERE user_id = ? " +
                        "AND reservation_date >= DATE_SUB(CURDATE(), INTERVAL 365 DAY) " +
                        "GROUP BY MONTH(reservation_date) " +
                        "ORDER BY MONTH(reservation_date)";
                labels = new String[] { "1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月" };
                break;
            default:
                return getUserTrendData(userId, "近一周");
        }

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();

            // 初始化数据数组
            int[] dataPoints = new int[labels.length];

            while (rs.next()) {
                int hours = rs.getInt("hours");

                if (period.equals("近一周")) {
                    int dayOfWeek = rs.getInt("day_of_week"); // 1=Sunday, 2=Monday, etc.
                    if (dayOfWeek >= 1 && dayOfWeek <= 7) {
                        dataPoints[dayOfWeek - 1] = hours; // 直接映射到数组索引
                    }
                } else if (period.equals("近一月")) {
                    int weekNum = rs.getInt("week_num");
                    if (weekNum >= 1 && weekNum <= 5) {
                        dataPoints[weekNum - 1] = hours;
                    }
                } else if (period.equals("近一年")) {
                    int monthNum = rs.getInt("month_num");
                    if (monthNum >= 1 && monthNum <= 12) {
                        dataPoints[monthNum - 1] = hours;
                    }
                }
            }

            trendData.put("labels", labels);
            trendData.put("data", dataPoints);
        }

        return trendData;
    }

    /**
     * 获取用户时间段偏好数据
     */
    public Map<String, Object> getUserTimeSlotData(int userId, String period) throws SQLException {
        Map<String, Object> timeSlotData = new HashMap<>();

        String dateCondition;
        switch (period) {
            case "近一周":
                dateCondition = "AND reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)";
                break;
            case "近一月":
                dateCondition = "AND reservation_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)";
                break;
            case "近一年":
                dateCondition = "AND reservation_date >= DATE_SUB(CURDATE(), INTERVAL 365 DAY)";
                break;
            default:
                dateCondition = "AND reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)";
        }

        String sql = "SELECT " +
                "CASE " +
                "WHEN start_time < '12:00:00' THEN '上午' " +
                "WHEN start_time < '18:00:00' THEN '下午' " +
                "ELSE '晚上' END as time_slot, " +
                "COUNT(*) as count " +
                "FROM reservations WHERE user_id = ? " + dateCondition + " " +
                "GROUP BY time_slot";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();

            int[] values = new int[3]; // 上午、下午、晚上
            String[] timeSlots = { "上午", "下午", "晚上" };

            while (rs.next()) {
                String timeSlot = rs.getString("time_slot");
                int count = rs.getInt("count");

                for (int i = 0; i < timeSlots.length; i++) {
                    if (timeSlots[i].equals(timeSlot)) {
                        values[i] = count;
                        break;
                    }
                }
            }

            // 转换为百分比
            int total = values[0] + values[1] + values[2];
            int[] percentages = new int[3];
            if (total > 0) {
                for (int i = 0; i < 3; i++) {
                    percentages[i] = (int) Math.round((double) values[i] * 100 / total);
                }
            }

            timeSlotData.put("labels", timeSlots);
            timeSlotData.put("values", percentages);
        }

        return timeSlotData;
    }

    /**
     * 获取用户考勤统计数据
     */
    public Map<String, Object> getUserAttendanceData(int userId, String period) throws SQLException {
        Map<String, Object> attendanceData = new HashMap<>();

        String dateCondition;
        switch (period) {
            case "近一周":
                dateCondition = "AND r.reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)";
                break;
            case "近一月":
                dateCondition = "AND r.reservation_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)";
                break;
            case "近一年":
                dateCondition = "AND r.reservation_date >= DATE_SUB(CURDATE(), INTERVAL 365 DAY)";
                break;
            default:
                dateCondition = "AND r.reservation_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)";
        }

        String sql = "SELECT " +
                "CASE " +
                "WHEN a.is_checked_in = 1 AND a.is_checked_out = 1 AND " +
                "     TIME(a.check_in_time) <= ADDTIME(r.start_time, '00:15:00') AND " +
                "     TIME(a.check_out_time) >= SUBTIME(r.end_time, '00:15:00') THEN '正常' " +
                "WHEN a.is_checked_in = 1 AND a.is_checked_out = 1 AND " +
                "     TIME(a.check_in_time) > ADDTIME(r.start_time, '00:15:00') THEN '迟到' " +
                "WHEN a.is_checked_in = 1 AND a.is_checked_out = 1 AND " +
                "     TIME(a.check_out_time) < SUBTIME(r.end_time, '00:15:00') THEN '早退' " +
                "WHEN a.is_checked_in = 0 OR a.attendance_id IS NULL THEN '爽约' " +
                "ELSE '其他' END as attendance_type, " +
                "COUNT(*) as count " +
                "FROM reservations r LEFT JOIN attendance_records a ON r.reservation_id = a.reservation_id " +
                "WHERE r.user_id = ? " + dateCondition + " AND r.status IN (1, 2) " +
                "GROUP BY attendance_type";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();

            String[] labels = { "正常", "迟到", "早退", "爽约" };
            int[] values = new int[4];

            while (rs.next()) {
                String attendanceType = rs.getString("attendance_type");
                int count = rs.getInt("count");

                for (int i = 0; i < labels.length; i++) {
                    if (labels[i].equals(attendanceType)) {
                        values[i] = count;
                        break;
                    }
                }
            }

            attendanceData.put("labels", labels);
            attendanceData.put("values", values);
        }

        return attendanceData;
    }

    /**
     * 获取用户信誉分
     */
    public int getUserCreditScore(int userId) throws SQLException {
        String sql = "SELECT credit_score FROM users WHERE user_id = ?";

        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return rs.getInt("credit_score");
            }
        }

        return 100; // 默认初始分数
    }
}
